package net.tycmc.cty.ctyms.web.Service;

import java.util.HashMap;
import java.util.Map;

import com.tycmc.functions.util.MapGetter;

import net.tycmc.bulb.common.util.DateUtil;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.web.Dao.dataBaseTranplatDao;

public class dataBaseTranplatService {
	private dataBaseTranplatDao dbtpd;
	
	public dataBaseTranplatDao getDbtpd() {
		return dbtpd;
	}

	public void setDbtpd(dataBaseTranplatDao dbtpd) {
		this.dbtpd = dbtpd;
	}
	
	/**
	 * sql样例
	 * @return
	 */
	public Map<String,Object> getStyleOfSimPle(){
		Map<String,Object> map=new HashMap<String, Object>();
		String ServerName = MapGetter.getString(GlobalDBVar.dbMSBak,"dbservername");
		String UserName= MapGetter.getString(GlobalDBVar.dbMSBak,"UserName");
		String Password= MapGetter.getString(GlobalDBVar.dbMSBak,"Password");
		String DatabaseName= MapGetter.getString(GlobalDBVar.dbMSBak,"DatabaseName");
		//设备健康状态
		String table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.Message_VclState_Health)"; 
		String sql=" insert into Message_VclState_Health (MsgVH_VclID,MsgVH_State,MsgVH_UntilTime) select MsgVH_VclID,MsgVH_State,MsgVH_UntilTime from "+table;
		map.put("health",sql);
		//保养提示
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.Mtn_Alarm_InfoNew)"; 
		sql="insert into Mtn_Alarm_InfoNew(MtnAN_VclT_ID,MtnAN_VclID,MtnAN_AlarmDate,MtnAN_IsKmForst,MtnAN_IsWorkForst,MtnAN_IsGapForst,MtnAN_KmAlarm,MtnAN_WorkAlarm,MtnAN_GapAlarm)" +
				" select MtnAN_VclT_ID,MtnAN_VclID,MtnAN_AlarmDate,MtnAN_IsKmForst,MtnAN_IsWorkForst,MtnAN_IsGapForst,MtnAN_KmAlarm,MtnAN_WorkAlarm,MtnAN_GapAlarm from "+table+" where MtnAN_AlarmDate>='"+DateUtil.addDay(-1)+"'";
		map.put("maintTip",sql);
		//保养预测
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.MtnForst_InfoNew)"; 
		sql="insert into MtnForst_InfoNew (MtnFN_MsgTime,MtnFN_VclID,MtnFN_VclT_ID,MtnFN_DatePre,MtnFN_IsKmForst,MtnFN_IsWorkForst,MtnFN_IsGapForst,MtnFN_KmPre,MtnFN_WorkPre,MtnFN_GapPre) " +
		"select  MtnFN_MsgTime,MtnFN_VclID,MtnFN_VclT_ID,MtnFN_DatePre,MtnFN_IsKmForst,MtnFN_IsWorkForst,MtnFN_IsGapForst,MtnFN_KmPre,MtnFN_WorkPre,MtnFN_GapPre from "+table;
		map.put("maintForst", sql);
		//数据缺失监控--------------------------
		//参数未统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.VclNoCollectParam)"; 
		sql=" insert into VclNoCollectParam (Vncp_vcl_id,Vncp_vclno,Vncp_msgTime,Vncp_tmnlno) select Vncp_vcl_id,Vncp_vclno,Vncp_msgTime,Vncp_tmnlno from "+table+" where Vncp_msgTime='"+DateUtil.addDay(-1)+"'";
		map.put("VclNoCollectParam",sql);
		//采集频率统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.VclCollectFre)"; 
		sql=" insert into VclCollectFre (Vclf_vcl_id,Vclf_vclno,Vclf_msgTime,Vclf_tmnlno,Vclf_keyOnTime,Vclf_DataRecordNum) " +
				"select Vclf_vcl_id,Vclf_vclno,Vclf_msgTime,Vclf_tmnlno,Vclf_keyOnTime,Vclf_DataRecordNum from "+table+" where Vclf_msgTime='"+DateUtil.addDay(-1)+"'";
		map.put("VclCollectFre",sql);
		//档位及车速统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.DB2OfVcl)"; 
		sql=" insert into DB2OfVcl (DB2OV_vcl_id,DB2OV_vclno,DB2OV_msgTime,DB2OV_tmnlno,DB2OV_ECMrunTime) " +
				"select DB2OV_vcl_id,DB2OV_vclno,DB2OV_msgTime,DB2OV_tmnlno,DB2OV_ECMrunTime from "+table+" where DB2OV_msgTime='"+DateUtil.addDay(-1)+"'";
		map.put("DB2OfVcl",sql);
		//工作循环区域统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.DCFZOfVcl)"; 
		sql=" insert into DCFZOfVcl (DCFZOV_vcl_id,DCFZOV_vclno,DCFZOV_msgTime,DCFZOV_tmnlno,DCFZOV_ECMrunTime) " +
				"select DCFZOV_vcl_id,DCFZOV_vclno,DCFZOV_msgTime,DCFZOV_tmnlno,DCFZOV_ECMrunTime from "+table+" where DCFZOV_msgTime='"+DateUtil.addDay(-1)+"'";
		map.put("DCFZOfVcl",sql);
		//载重利用率为空统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.LoadRateOfVcl)"; 
		sql=" insert into LoadRateOfVcl (Lrov_vcl_id,Lrov_vclno,Lrov_msgTime,Lrov_tmnlno,Lrov_ECMrunTime) " +
				"select Lrov_vcl_id,Lrov_vclno,Lrov_msgTime,Lrov_tmnlno,Lrov_ECMrunTime from "+table+" where Lrov_msgTime='"+DateUtil.addDay(-1)+"'";
		map.put("LoadRateOfVcl",sql);
		//里程选择不正确统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.RunKmOfVcl)"; 
		sql=" insert into RunKmOfVcl (RKOV_vcl_id,RKOV_vclno,RKOV_tmnlno,RKOV_msgTime,RKOV_iRunKmExact_ECM,RKOV_iRunKmECM,RKOV_iRunKmExact,RKOV_iRunKmGPS,RKOV_iRunKmECU) " +
				"select RKOV_vcl_id,RKOV_vclno,RKOV_tmnlno,RKOV_msgTime,RKOV_iRunKmExact_ECM,RKOV_iRunKmECM,RKOV_iRunKmExact,RKOV_iRunKmGPS,RKOV_iRunKmECU from "+table+" where RKOV_msgTime='"+DateUtil.addDay(-1)+"'";
		map.put("RunKmOfVcl",sql);
		//时效统计
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.EffState)"; 
		sql=" insert into EffState (EffS_VclID,EffS_StateTime,EffS_IntoTime,EffS_OutTime,EffS_IntoFenceID,EffS_OutFenceID,EffS_OperName,EffS_Time,EffS_EgnTime,EffS_AvgSpeed,EffS_AvgLoad,EffS_AvgOil,EffS_IntoFenceNum,EffS_OutFenceNum) " +
				"select EffS_VclID,EffS_StateTime,EffS_IntoTime,EffS_OutTime,EffS_IntoFenceID,EffS_OutFenceID,EffS_OperName,EffS_Time,EffS_EgnTime,EffS_AvgSpeed,EffS_AvgLoad,EffS_AvgOil,EffS_IntoFenceNum,EffS_OutFenceNum from "+table+" where EffS_StateTime>='"+DateUtil.addDay(-1)+"'";
		map.put("EffState",sql);
		//档位车速分析
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.vcldb2_"+DateUtil.addDay(-1).replaceAll("-","").substring(0,6)+")"; 
		sql=" insert into vcldb2_"+DateUtil.addDay(-1).replaceAll("-","").substring(0,6)+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_0G1STIMES,VclDB2_0G3STIMES,VclDB2_TOTALCNT,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime) " +
				"select VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_0G1STIMES,VclDB2_0G3STIMES,VclDB2_TOTALCNT,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime from "+table+" where VclDB2_StateTime>='"+DateUtil.addDay(-1)+"'";
		map.put("vcldb2",sql);
		//设备每天道路信息
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.Vcl_RoadInfo_"+DateUtil.addDay(-1).replaceAll("-","")+")"; 
		sql=" insert into Vcl_RoadInfo_"+DateUtil.addDay(-1).replaceAll("-","")+" (VclRI_Vcl_ID,VclRI_Altitude,VclRI_GPSMile,VclRI_WorkTime,VclRI_PstnTime,VclRI_EgnWorkTime,VclRI_WheelSpeed,VclRI_MsgTime) " +
				"select VclRI_Vcl_ID,VclRI_Altitude,VclRI_GPSMile,VclRI_WorkTime,VclRI_PstnTime,VclRI_EgnWorkTime,VclRI_WheelSpeed,VclRI_MsgTime from "+table;
		map.put("RoadInfo",sql);
		//油门开度
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.OilSwitchOpe_"+DateUtil.addDay(-1).replaceAll("-","")+")"; 
		sql=" insert into OilSwitchOpe_"+DateUtil.addDay(-1).replaceAll("-","")+" (OSO_VclID,OSO_Date,OSO_AllCounts,OSO_ValidCounts,OSO_Zero,OSO_UnderTen,OSO_TenToTwe,OSO_TweToThi,OSO_ThiToFor,OSO_ForToFif,OSO_FifToSix,OSO_SixToSev,OSS_SevToEig,OSO_EigToNin,OSO_NinToHun) " +
				"select OSO_VclID,OSO_Date,OSO_AllCounts,OSO_ValidCounts,OSO_Zero,OSO_UnderTen,OSO_TenToTwe,OSO_TweToThi,OSO_ThiToFor,OSO_ForToFif,OSO_FifToSix,OSO_SixToSev,OSS_SevToEig,OSO_EigToNin,OSO_NinToHun from "+table;
		map.put("OilSwitchOpe",sql);
		//路况值变化
		table="openrowset('SQLOLEDB','"+ServerName+"';'"+UserName+"';'"+Password+"',"+DatabaseName+".dbo.RoadChangeStat_"+DateUtil.addDay(-1).replaceAll("-","")+")"; 
		sql=" insert into RoadChangeStat_"+DateUtil.addDay(-1).replaceAll("-","")+" (rcs_vclid,rcs_plain,rcs_hill,rcs_marea,rcs_highland,rcs_tonums,rcs_infen,rcs_outfen,rcs_fltone,rcs_fltsec,rcs_fltthi,rcs_beplace,rcs_stattime) " +
				"select rcs_vclid,rcs_plain,rcs_hill,rcs_marea,rcs_highland,rcs_tonums,rcs_infen,rcs_outfen,rcs_fltone,rcs_fltsec,rcs_fltthi,rcs_beplace,rcs_stattime from "+table;
		map.put("RoadChangeStat",sql);
		
		return map;
	}
	/**
	 * 执行sql脚本
	 * @return
	 */
	public int DoSql(String sql){
		return dbtpd.DoSql(sql);
	}
}
